import sqlite3


# Main source path for files.
app_path = "D:\\Eigene Datein\\Python\\TIGS DB\\"
source_path = app_path + "files\\"

FILENAME = source_path + "tigs_db_08082011.sdb3"
conn = sqlite3.connect(FILENAME)

def create_db_structure(conn):
    """Creates a sqlite DB of the TIGS_Table."""

    sql = """CREATE TABLE TIGS_TABLE (
        id INTEGER PRIMARY KEY NOT NULL,
        ta_id TEXT NOT NULL,
        tube_id TEXT NOT NULL,
        function TEXT NOT NULL,
        gus_cells TEXT NOT NULL,
        haustoria_amount TEXT NOT NULL,
        experiment_name TEXT NOT NULL,
        cultivar_name TEXT NOT NULL,
        hours_after_inoculation TEXT NOT NULL,
        pathogen_name TEXT NOT NULL,
        link_to_excel_sheet TEXT NOT NULL
    );
    CREATE INDEX ix_tigs_unique ON TIGS_Table (ta_id, tube_id, experiment_name);
    """
    conn.executescript(sql)
    conn.commit()

    # Read source data, tab del.
    f_data = open(source_path + 'tigs_db_08082011.txt', 'r')
    data = f_data.readlines()
    f_data.close()

    tigs_dict = []
    for entry in data:
        entry = entry.strip()
        entry = entry.split('\t')


        tigs_dict.append({
                        "ta_id" : entry[0],
                        "tube_id" : entry[1],
                        "function" : entry[2],
                        "gus_cells" : entry[3],
                        "haustoria_amount" : entry[4],
                        "experiment_name" : entry[5],
                        "cultivar_name" : entry[6],
                        "hours_after_inoculation" : entry[7],
                        "pathogen_name" : entry[8],
                        "link_to_excel_sheet" : entry[9]})


    sql2 = """
        INSERT OR IGNORE INTO TIGS_Table (
            ta_id,
            tube_id,
            function,
            gus_cells,
            haustoria_amount,
            experiment_name,
            cultivar_name,
            hours_after_inoculation,
            pathogen_name,
            link_to_excel_sheet
        ) VALUES (
            :ta_id,
            :tube_id,
            :function,
            :gus_cells,
            :haustoria_amount,
            :experiment_name,
            :cultivar_name,
            :hours_after_inoculation,
            :pathogen_name,
            :link_to_excel_sheet
        )"""
    conn.executemany(sql2, tigs_dict)
    conn.commit()
    conn.close()

def get_data_from_db(conn, sql):
    """Get data from TIGS DB after request."""

    conn = sqlite3.connect(FILENAME)
    #sql = sql
    cur = conn.cursor()
    cur.execute(sql)
    rows = cur.fetchall()

    request_dict = {}
    id = 1
    for entry in rows:
        request_dict[id] = entry
        id += 1
    conn.close()

    return request_dict

def update_db(conn, projects):
    """Update old DB if there are new projects."""

    project_list = get_project_from_db(conn)

    for files in projects:
        try:
            os.remove('projects\\' + files + '.txt')

        except WindowsError:
            pass

        f = open('test.txt', 'r')
        data = f.read()
        if files not in data:
            f.close()
            f = open('test.txt', 'a')
            f.write(files + '\n')
        f.close()

    anno_dict = []
    for project in projects:
        if project not in project_list:
            try:
                pictures = os.listdir(directory + project + "\\cutout")
                for picture in pictures:
                    if not re.search(r"_.jpg\Z", str(picture)):

                        shot = re.split(r"\w_", picture, 1)
                        cell = 'True'
                        hau = 'False'

                        anno_dict.append({'project_name':project,
                                          'picture_name':picture,
                                          'cell_anno':cell,
                                          'haustoria_anno':hau,
                                          'shot_name':str(shot[0])})

            except WindowsError:
                pass

    sql2 = """
        INSERT OR IGNORE INTO annotation (
            project_name,
            picture_name,
            cell_anno,
            haustoria_anno,
            shot_name
        ) VALUES (
            :project_name,
            :picture_name,
            :cell_anno,
            :haustoria_anno,
            :shot_name
        );"""

    conn.executemany(sql2, anno_dict)
    conn.commit()
    conn.close()



def delet_db(conn, projects):
    """Delete data in DB."""

    conn = sqlite3.connect(FILENAME)
    cur = conn.cursor()
    for name in projects:
        cur.execute("""DELETE FROM annotation WHERE
                     project_name =?""",
                     (name,))
        # Delete all txt files
        try:
            for line in fileinput.input("test.txt",inplace=1):
                line = line.strip()
                if not name in line:
                    print line

            os.remove('projects\\' + name + '.txt')

        except WindowsError:
            pass

    conn.commit()
    conn.close()

#create_db_structure(conn)
#get_data_from_db(conn)
#update_db(conn)
#delet_db(conn)

